package com.java.dao.impl;


import com.java.dao.BasicDao;
import com.java.dao.GoodsDao;
import com.java.domain.Goods;
import com.java.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GoodsDaoImpl extends BasicDao<Goods> implements GoodsDao {
    private String sql;
    private QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());

    // 获取搜索框查询出来的商品总数
    private Integer searchCount;
    public Integer getSearchCount() {
        return searchCount;
    }

    public void setSearchCount(Integer searchCount) {
        this.searchCount = searchCount;
    }

    @Override
    public List<Goods> queryGoods() {
        sql ="select gid,gName,gImg,gPrice,num,type,gDetails,gColour from goods";
        return queryMulti(sql,Goods.class);
    }

    @Override
    public Integer getGoodsCount() {
        sql = "select count(*) from goods";
        try {
            return ((Long)runner.query(sql,new ScalarHandler<>())).intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Goods> getGoodsPageList(int page, int rows) {
        sql = "select gid,gName,gImg,gPrice,num,type,gDetails,gColour from goods limit ?,?";
        try {
            return runner.query(sql,new BeanListHandler<>(Goods.class),(page-1)*rows,rows);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int delGoods(Integer gid) {
        sql = "delete from goods where gid = ?";
        try {
            return runner.update(sql,gid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int updateGoods(Goods goods) {
        sql = "update goods set gname=?,gimg=?,gprice=?,num=?,`type`=?,gdetails=?,gcolour=? where gid=?";
        try {
            return runner.update(sql,goods.getgName(),goods.getgImg(),goods.getgPrice(),goods.getNum(),
                    goods.getType(),goods.getgDetails(),goods.getgColour(),goods.getGid());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int addGoods(Goods goods) {
        sql = "insert into goods(gName,gImg,gPrice,num,`type`,gDetails,gColour) values (?,?,?,?,?,?,?)";
        try {
            return runner.update(sql,goods.getgName(),goods.getgImg(),goods.getgPrice(),
                    goods.getNum(),goods.getType(),goods.getgDetails(),goods.getgColour());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Goods> goodsLike(String gname, Integer lowernum, Integer uppernum,Integer page,Integer rows) {
        List<Object> list = new ArrayList<>();
        sql = "select gid,gName,gImg,gPrice,num,`type`,gDetails,gColour from goods where 1 = 1";
        if(gname != null && lowernum!=null && uppernum != null){
            sql += " and gname like ? and gprice >= ? and gprice <= ?";
            list.add("%"+gname+"%");
            list.add(lowernum);
            list.add(uppernum);
        }

        if(gname != null && lowernum==null && uppernum == null){
            sql += " and gname like ?";
            list.add("%"+gname+"%");
        }

        if(gname == null && lowernum!=null && uppernum != null){
            sql += " and gprice >= ? and gprice <= ?";
            list.add(lowernum);
            list.add(uppernum);
        }

        try {
            String sqlCount = "select count(*) "+sql.substring(sql.indexOf("from"));
            System.out.println(sqlCount);

            setSearchCount(((Long)runner.query(sqlCount,new ScalarHandler<>(),list.toArray())).intValue());

            sql += " limit ?,?";
            list.add((page-1)*rows);
            list.add(rows);

            return runner.query(sql,new BeanListHandler<>(Goods.class),list.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int delAll(String ids) {//1,2,3
        String[] split = ids.split(",");
        sql = "delete from goods where gid in (";

        for (int i = 0; i < split.length; i++) {
            sql += " ?,";
        }

        sql += ")";

        String substring = sql.substring(0, sql.lastIndexOf(","))+")";

        System.out.println(substring);
        try {
            return runner.update(substring,split);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

//    @Override
//    public Goods singleGoods(int gid) {
//        sql ="select gid,gName,gImg,gPrice,num,type,gDetails,gColour from goods where gid=?";
//        return querySingle(sql,Goods.class,gid);
//    }
}
